﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using HotelManager.Data_Transfer_Layer;

namespace HotelManager.Data_Access_Layer
{
    class phongDAO
    {
        #region Variables
        private OleDbConnection conn;
        private string error;
        #endregion

        #region Properties
        public string Error
        {
            get { return error; }
            set { error = value; }
        }
        #endregion

        public phongDAO()
        {
            error = "";
            try
            {
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\\hotel.accdb";
                conn = new OleDbConnection(strConn);
                conn.Open();
            }
            catch (Exception ex)
            {
                Error = "Không kết nối được đến cơ sở dữ liêu";
            }
        }

        public phongDTO[] get()
        {
            phongDTO[] objs;
            objs = new phongDTO[100];
            try
            {
                string sql = "SELECT * FROM phong";
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                int i = 0, n = dt.Rows.Count;
                objs = new phongDTO[n];
                while (reader.Read())
                {
                    objs[i] = new phongDTO();
                    objs[i].P_id = reader.GetInt32(0);
                    objs[i].Lp_id = reader.GetInt32(2);                    
                    objs[i].Kv_id = reader.GetInt32(4);
                    objs[i].P_tinhtrang = reader.GetString(3).ToString();
                    objs[i].P_tenphong = reader.GetString(1).ToString();
                    i++;
                }
            }
            catch (Exception ex)
            {

            }
            return objs;
        }

        public phongDTO[] List_current(int kv_id, int lp_id)
        {
            phongDTO[] objs;
            objs = new phongDTO[100];
            try
            {
                string sql = "";
                if (kv_id != -1 && lp_id != -1)
                    sql = "SELECT * FROM phong WHERE lp_id=" + lp_id + " AND kv_id=" + kv_id;
                else
                {
                    if (kv_id == -1 && lp_id!=-1)
                        sql = "SELECT * FROM phong WHERE lp_id=" + lp_id;
                    else
                    {
                        if (lp_id == -1 && kv_id != -1)
                            sql = "SELECT * FROM phong WHERE kv_id=" + kv_id;
                        else
                        {
                            if (kv_id == -1 && lp_id == -1)
                                sql = "SELECT * FROM phong";
                        }
                    }
                }
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                int i = 0, n = dt.Rows.Count;
                objs = new phongDTO[n];
                while (reader.Read())
                {
                    objs[i] = new phongDTO();
                    objs[i].P_id = reader.GetInt32(0);
                    objs[i].Lp_id = reader.GetInt32(2);
                    objs[i].Kv_id = reader.GetInt32(4);
                    objs[i].P_tinhtrang = reader.GetString(3).ToString();
                    objs[i].P_tenphong = reader.GetString(1).ToString();
                    i++;
                }
            }
            catch (Exception ex)
            {

            }
            return objs;
        }

        public phongDTO View_Detail(int p_id)
        {
            phongDTO obj = new phongDTO();
            try
            {
                string sql = "SELECT * FROM phong WHERE p_id=" + p_id;
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    obj.Lp_id = reader.GetInt32(2);
                    obj.P_tenphong = reader.GetString(1).ToString();
                    obj.Kv_id = reader.GetInt32(4);
                    obj.P_tinhtrang = reader.GetString(3).ToString();
                }
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            return obj;
        }

        public phongDTO View_DetailbyName(string p_tenphong)
        {
            phongDTO obj = new phongDTO();
            try
            {
                string sql = "SELECT * FROM phong WHERE p_tenphong='" + p_tenphong + "'";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    obj.P_id = reader.GetInt32(0);
                    obj.Lp_id = reader.GetInt32(2);
                    obj.Kv_id = reader.GetInt32(4);
                    obj.P_tinhtrang = reader.GetString(3).ToString();
                }
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            return obj;
        }

        public void Delete(phongDTO obj)
        {
            try
            {
                string sql = "DELETE FROM phong WHERE p_id=" + obj.P_id;
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

        public void Insert(phongDTO obj)
        {
            try
            {
                string sql = "INSERT INTO phong(p_tenphong,lp_id,p_tinhtrang,kv_id) VALUES('" + obj.P_tenphong + "'," + obj.Lp_id + ",'" + obj.P_tinhtrang + "'," + obj.Kv_id + ")";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

        public void Update(phongDTO obj)
        {
            try
            {
                string sql1 = "UPDATE phong SET p_tenphong='" + obj.P_tenphong + "',p_tinhtrang='" + obj.P_tinhtrang + "',lp_id=" + obj.Lp_id + ",kv_id=" + obj.Kv_id + " WHERE p_id=" + obj.P_id;
                OleDbCommand cmd = new OleDbCommand(sql1, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

        public void Update_tinhtrang(string tinhtrang,int p_id)
        {
            try
            {
                string sql1 = "UPDATE phong SET p_tinhtrang='" + tinhtrang + "' WHERE p_id=" + p_id;
                OleDbCommand cmd = new OleDbCommand(sql1, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }
    }
}
